/**
 * Created by yudongyang on 2016/2/27 15:07.
 * mail: yudongyang@kehaoinfo.com
 **/

package com.wujie.activies.util;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;

/**
 * Created by yudongyang on 2016/2/27.
 */
public class POIHelper {


    public void readExcel(File file) throws IOException, InvalidFormatException {
        if (file.isFile()) {
//            readFile(file);
        } else {
            File[] files = file.listFiles();
            for (File t : files) {
                if (t.isDirectory()) {
                    readExcel(t);
                } else {
                    if (t.getName().endsWith("xls") || t.getName().endsWith("xlsx")) {
//                        readFile(t);
                    }
                }
            }
        }
    }
    public String getCellValue(Cell cell) {

        if (cell == null)
            return "";

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

            return cell.getStringCellValue();

        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

            return cell.getCellFormula();

        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

            return cell.getNumericCellValue() + "";

        }
        return "";
    }

    private boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }
    public String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }

    private String getValue(Sheet sheet, String col) {
        try {
            CellReference t = new CellReference(col);
            if (isMergedRegion(sheet, t.getRow(), t.getCol())) {
                return getMergedRegionValue(sheet, t.getRow(), t.getCol());
            } else {
                Cell cell = sheet.getRow(t.getRow()).getCell(t.getCol());
                return getRightTypeCell(cell).toString();
            }
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 去掉来源于网页的excel中的显示为非半角非全角的空格。
     * 链接： http://blog.csdn.net/bob007/article/details/27098875
     * @param str
     * @return
     * @throws UnsupportedEncodingException
     */
    private String deleteUTF8InWebSpace(String str) throws UnsupportedEncodingException {
        byte bytes[] = {(byte) 0xC2,(byte) 0xA0};
        String UTFSpace = new String(bytes,"utf-8");
        return str.replaceAll(UTFSpace,"");
    }


    /** 检查单元格是否为空
     * @param sheet
     * @param row
     * @param col
     * @return
     */
    private boolean checkNullCell(Sheet sheet,int row,int col){
        return sheet == null ||
                sheet.getRow(row) == null ||
                checkNullCell(sheet.getRow(row).getCell(col));
    }

    private boolean checkNullCell(Cell cell){
        return cell == null ||
                cell.getCellType() == Cell.CELL_TYPE_BLANK ||
                (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().equals(""));
    }

    /**
     *
     * @param cell 一个单元格的对象
     * @return 返回该单元格相应的类型的值
     */
    private Object getRightTypeCell(Cell cell){
        Object object = null;
        switch(cell.getCellType())
        {
            case Cell.CELL_TYPE_STRING :
            {
                object=cell.getStringCellValue();
                break;
            }
            case Cell.CELL_TYPE_NUMERIC :
            {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                object=cell.getNumericCellValue();
                break;
            }

            case Cell.CELL_TYPE_FORMULA :
            {
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                object=cell.getNumericCellValue();
                break;
            }

            case Cell.CELL_TYPE_BLANK :
            {
                cell.setCellType(Cell.CELL_TYPE_BLANK);
                object=cell.getStringCellValue();
                break;
            }
        }
        return object;
    }
}
